-- 上机练习一
BEGIN;
INSERT INTO t_student ( id,student_name,gender, birthday,age,classID,begin_year) VALUES ( 3008,'张三','女','2003-10-12', 20,1,2023);
UPDATE t_student SET student_name = '李四', age = 22 WHERE studentID = 3008;
DELETE FROM t_student WHERE age < 18;
COMMIT;
-- 上机练习二
create database COLLATE utf8mb4_unicode_ci;
CREATE TABLE account(
id,int AUTO_INCREMENT PRIMARY KEY,
account_name VARCHAR(20) NOT NULL,
phone VARCHAR(11) NOT NULL,
balance DECIMAL(30,5)
);
INSERT INTO Account VALUES('6225214702630552','张三','13541522563',100);
INSERT INTO Account VALUES('6225214702630562','李四','13012123443',100);
UPDATE account SET balance = balance - 200 WHERE id = '6225214702630552';
UPDATE account SET balance = balance + 200 WHERE id = '6225214702630562';
commit;
-- 上机练习三
create table student(
id,INT AUTO_INCREMENT PRIMARY KEY,
student_name VARCHAR(20) NOT NULL,
phone VARCHAR(11) NOT NULL,
gender ENUM('男','女')
)

-- 创建存储过程，插入100000条件记录
DELIMITER //
CREATE PROCEDURE insert_100000_rows()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 100000 DO
INSERT INTO student(id,student_name) VALUES (i, CONCAT('张三',i),'13193010765','男');
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
EXPLAIN select * from student where student_name='张三100000';
CREATE UNIQUE index student_name_index on student (student_name);
EXPLAIN select * from student where student_name='张三100000';

-- 上机练习四
CREATE VIEW view_student_san AS
SELECT studentID, student_name
FROM t_student
WHERE student_name = '张耀仁';